/**
 * @copyright Copyright (c) 2025 
 * CSG Digital Grid Technology (Guangdong) Co., Ltd.
 * @author klg
 * @brief 数据库操作相关头文件，包含数据库结构定义和接口声明
 */
#ifndef __DATABASE_H__
#define __DATABASE_H__

#include "common.h"

#include "ll.h"
#include "cJSON.h"

#include "sqlite_api.h"



typedef struct database_json {
#define HISTORY_LOG_DAY 30      /* 默认记录30天 */
    char mqtt_broker[128];      /* mqtt broker地址和端口 */
    char database_dir[128];     /* 数据库文件路径 */
    int history_log_day;        /* 冻结数据最大保存天数 */
    char time_zone[16];         /* 网关时区 */
    int log_file_enable;        /* 1表示开启log文件记录，0表示关闭log文件记录 */
    int log_file_size_max_mbyte;/* log文件最大多少兆字节 */
    int rsyslog_enable;         /* rsyslog是否开启 */
    int json_unformatted;       /* 发送json数据包时候是否格式化，1表示取消格式化，0表示格式化 */
    int license_flag;           /* 1表示license校验成功 */
    int vacuum_interval;        /* 执行vacuum的间隔 */
    // int vacuum_enable;         /* 1表示开启vacuum，0表示关闭vacuum */
} database_json_t;

//database_json_t db_config;


struct model_property_s {
    char name[50];
    char type[50];
    char unit[50];
    char deadzone[50];
    char ratio[10];
    char isreport[2];
    char userdefine[100];
};

struct dev_property_s {
    char guid[50];
    char dev[100];
    char model[50];
    char port[32];
    char addr[20];
    char desc[1024];
    char manuID[50];
    char manuName[100];
    char ProType[50];
    char deviceType[50];
    //char deviceName[100];
    char isReport[2];
    char nodeID[50];
    char productID[50];
    char appname[50];
};


struct model_list_node_s {
    char model_name[64];        /* 模型名 */
    cJSON *jn_model;            /* 模型信息，json格式 */
    cJSON *jn_dev;              /* 模型生成的设备 */
    cJSON *jn_realtime_data;    /* 设备实时数据 */
    cJSON *jn_soe_subscribe;    /* 设备的SOE订阅事件 */
};

extern ll_t *model_list;    /* list，存储model_list_node_s，是设备模型和设备列表的总list */


#define FROZEN_TABLE_STRUCT "CREATE TABLE IF NOT EXISTS %s (" \
         "sn                INTEGER PRIMARY KEY AUTOINCREMENT,"            \
         "frozentype        CHAR(20)                            NOT NULL," \
         "timestamp         CHAR(50)                            NOT NULL," \
         "timestartgather   CHAR(50)                            NOT NULL," \
         "timeendgather     CHAR(50)                            NOT NULL," \
         "additionalcheck   CHAR(50)                            NOT NULL," \
         "name              CHAR(50)                            NOT NULL," \
         "value             CHAR(50)                            NOT NULL," \
         "UNIQUE(frozentype, timestamp, timestartgather, timeendgather, additionalcheck, name));"

#define MODEL_TABLE_STRUCT "CREATE TABLE IF NOT EXISTS %s ("  \
         "sn                INT     PRIMARY KEY     NOT NULL," \
         "name              CHAR(50)                NOT NULL," \
         "type              CHAR(50)                NOT NULL," \
         "unit              CHAR(50)                NOT NULL," \
         "deadzone          CHAR(50)                NOT NULL," \
         "ratio             CHAR(10)                NOT NULL," \
         "isReport          CHAR(2)                 NOT NULL," \
         "userdefine        CHAR(100)               NOT NULL," \
         "UNIQUE(name, type));"

#define DEV_TABLE_STRUCT "CREATE TABLE IF NOT EXISTS %s ("    \
         "guid              INTEGER PRIMARY KEY AUTOINCREMENT,"            \
         "dev               CHAR(100)                           NOT NULL," \
         "model             CHAR(50)                            NOT NULL," \
         "port              CHAR(20)                            NOT NULL," \
         "addr              CHAR(20)                            NOT NULL," \
         "desc              CHAR(1024)                          NOT NULL," \
         "manuID            CHAR(50)                            NOT NULL," \
         "manuName          CHAR(100)                           NOT NULL," \
         "ProType           CHAR(50)                            NOT NULL," \
         "deviceType        CHAR(50)                            NOT NULL," \
         "isReport          CHAR(2)                             NOT NULL," \
         "nodeID            CHAR(50)                            NOT NULL," \
         "productID         CHAR(50)                            NOT NULL," \
         "appname           CHAR(50)                            NOT NULL," \
         "UNIQUE(model, port, addr, desc));"


int api_print_jn_realtime_data(char* app, char *tpc, char *msg, char *response);
int get_timestamp(char *buff);
int get_all_mode_name(void *para);
int get_mode_info(char *table_name, void *para);
int get_mode_info_key(char *table_name, void *para, char *name);
int get_dev_guid(char *table_name, char *mode, char *port, char *addr, char *desc, void *para);
int get_app_name_from_dev_table(char *table, char *dev, char *name);
int get_model_from_dev(char *dev, char *model);
int get_frozen_type_and_property_data(char *table_name, char *frozen_type, cJSON *property, cJSON *array, char *start_t, char *end_t, char *time_type);
int get_acq_files_data(char *t_name, char *property_name, char *property_val, cJSON *array);
int get_acq_files_data2(char *t_name, char *property_name, char *property_val, char *property_name2, char *property_val2, cJSON *array);
int get_dev_app_regist_info(char *table_name, int index, void *para);
int get_table_column(char *table_name, void *para);
int get_table_one_property(char *table_name, int index, char *property,void *para);
int table_exist_check(char *t_name);
int table_exist_check_model(char *t_name);
int creat_table_if_not_exist(char *sql, char *t_name);
int creat_table_if_not_exist_model(char *sql, char *t_name);
int save_frozen_data_to_db(char *t_name, char *name, char *value, char *timestamp, char *frozentype, char *timestartgather, char *timeendgather, char *additionalcheck);
int replace_frozen_data_to_db(char *t_name, char *name, char *value, char *timestamp, char *frozentype, char *timestartgather, char *timeendgather, char *additionalcheck);
int replace_soe_data_to_db(char *t_name, char *model, char *dev, char *event, char *timestamp, char *timestartgather, char *timeendgather, char *starttimestamp, char *endtimestamp, char *HappenSrc, char *IsNeedRpt, char *occurnum, char *EventLevel, char *RptStatus, char *SourType, char *data, char *extdata);
int replace_acq_files_to_db(char *t_name, char *CfgSN, char *AcqAddr, char *Port, char *CommBaud, char *CommPSW, char *ProType, char *DevType, char *WireType, char *RatedVolt, char *RatedCurr, char *CollectorAddr, char *AssetNum, char *RateCnt, char *PT, char *CT, char *AnnexInfo);
int save_dev_realtime_data(char *model, char *dev, char *name, char *val, char *quality, char *index, char *timestamp);
int get_unused_guid(char *table_name, char *guid);
int get_one_dev_realtime_data(char *model, char *dev, cJSON *jn);
int get_dev_one_realtime_data(char *model, char *dev, char *property, cJSON *jn);
int get_model_count(void);
int get_model_name_by_index(int index, char *name);
int delete_table(char *table_name);
int delete_table_one_item(char *table_name, char *item_key, char *item_val);
int save_property_to_model_table(char *t_name, struct model_property_s *p);
int save_property_to_dev_table(char *t_name, struct dev_property_s *p);
int update_model_list_when_model_regist(char *model_name);
int update_model_list_when_model_unregist(char *model_name);
#if 0
int update_model_list_when_dev_regist_unregist(char *model_name);
#else
int update_model_list_when_dev_regist_unregist(char *model_name, char *dev_name, int mode);
#endif

#define MODE_TABLE_HEAD "mode_"     /* 模型表是以mode_开头的表 */

#define SELECT_TABLE_NAME_LIKE "PRAGMA case_sensitive_like=ON; SELECT name FROM sqlite_master WHERE type='table' AND name LIKE '%s%%'"    /* %s用于替换以什么字符开头的表，这里两个%%是转义符，实际上是一个%，%在sqlite语句中表示通配符 */
#define SELECT_TABLE_NAME_LIKE_END "PRAGMA case_sensitive_like=ON; SELECT name FROM sqlite_master WHERE type='table' AND name LIKE '%%%s'"    /* %s用于替换以什么字符开头的表，这里两个%%是转义符，实际上是一个%，%在sqlite语句中表示通配符 */
#define SELECT_HISTORY_LIKE_TABLE "PRAGMA case_sensitive_like=ON; SELECT name FROM sqlite_master WHERE type='table' AND name LIKE '%|_history' escape '|'"
#define SELECT_MODEL_LIKE_WHAT_TABLE "PRAGMA case_sensitive_like=ON; SELECT name FROM sqlite_master WHERE type='table' AND name LIKE '%s|_%%|_%s' escape '|'"

#define SELECT_TABLE_CHECK_EXIST "SELECT count(*) FROM sqlite_master WHERE type=\"table\" AND name = \"%s\""
#define HISTORY_INSERT "INSERT INTO %s (\"sn\",\"devNum\",\"saveTime\",\"serviceId\",\"attrName\",\"value\") VALUES (%d, '%s', %d, '%s', '%s', '%s' ); "

#define REPLACE_HISTORY_FROZEN "replace into %s (frozentype, timestamp, timestartgather, timeendgather, additionalcheck, name, value) values('%s', '%s', '%s', '%s', '%s', '%s', '%s');"

#define HISTORY_FROZEN_INSERT "INSERT INTO %s (\"frozentype\",\"timestamp\",\"timestartgather\",\"timeendgather\",\"additionalcheck\",\"name\",\"value\") VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s'); "
#define MODEL_PROPERTY_INSERT "INSERT OR IGNORE INTO %s (\"sn\",\"name\",\"type\",\"unit\",\"deadzone\",\"ratio\",\"isReport\",\"userdefine\") VALUES (%d, '%s', '%s', '%s', '%s', '%s', '%s', '%s'); "
#define DEV_PROPERTY_INSERT "INSERT INTO %s (\"guid\",\"dev\",\"model\",\"port\",\"addr\",\"desc\",\"manuID\",\"manuName\",\"ProType\",\"deviceType\",\"isReport\",\"nodeID\",\"productID\",\"appname\") VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s'); "

#define DELETE_TABLE "DROP TABLE %s"
#define DELETE_TABLE_ONE_ITEM "DELETE FROM %s WHERE %s = '%s'"

#define DELETE_OLD_HISTORY "DELETE FROM %s WHERE timestamp < '%s'"
#define DELETE_OLD_DATA "DELETE FROM %s WHERE %s < '%s'"

#define SELECT_COLUMN "SELECT count(%s) FROM %s"

#define TABLE_EXIAT_CHECK "SELECT count(*) FROM sqlite_master WHERE type='table' AND name='%s'"

#define TABLE_EXIAT_CHECK_COLLATE_NOCASE "SELECT count(*) FROM sqlite_master WHERE type='table' AND name='%s' COLLATE NOCASE"

#define SELECT_TABLE_PROPERTY "SELECT %s FROM %s WHERE %s = '%s'"

#define SELECT_SERIAL_NUM "SELECT %s FROM %s WHERE serialNum = '%s'"
#define SELECT_LIMIT_OFFSET "SELECT %s FROM %s LIMIT %d OFFSET %d"

#define SELECT_TABLE "SELECT %s FROM %s"
#define SELECT_TABLE_NAME "SELECT %s FROM %s WHERE name = '%s'"
#define SELECT_TABLE_GET_GUID "SELECT %s FROM %s WHERE model = '%s' AND port = '%s' AND addr = '%s' AND desc = '%s'"
#define SELECT_TABLE_GET_GUID_NO_DESC "SELECT %s FROM %s WHERE model = '%s' AND port = '%s' AND addr = '%s' AND desc = ''"

#define SELECT_TABLE_GET_APP_NAME "SELECT %s FROM %s WHERE dev = '%s'"

//SELECT * FROM "ADC_frozen_10000" WHERE frozentype = 'min' AND (name = 'tgUa' OR name = 'tgUc')
//#define SELECT_TABLE_FROZENTYPE "SELECT %s FROM %s WHERE frozentype = '%s'"
//SELECT * FROM "ADC_frozen_10000" WHERE (frozentype = 'min') AND (name = 'tgUa' OR name = 'tgUc')  AND (timestamp >= '2021-06-08T00:00:00.000+0800' AND timestamp < '2021-06-08T19:00:00.000+0800')
#define SELECT_TABLE_FROZENTYPE "SELECT %s FROM %s WHERE frozentype = '%s' AND (%s >= '%s' AND %s < '%s')"
#define SELECT_TABLE_FROZENTYPE_ITEM "SELECT %s FROM %s WHERE frozentype = '%s' AND %s == '%s'"

#define SELECT_TABLE_ACQ_FILES  "SELECT %s FROM %s WHERE %s = '%s'"
#define SELECT_TABLE_ACQ_FILES2 "SELECT %s FROM %s WHERE %s = '%s' AND %s = '%s'"

#define SELECT_MAX_GUID "SELECT MAX(guid) AS max_guid FROM %s;"

#define DEV_PROPERTY_INSERT_V2 "INSERT INTO %s (\"dev\",\"model\",\"port\",\"addr\",\"desc\",\"manuID\",\"manuName\",\"ProType\",\"deviceType\",\"isReport\",\"nodeID\",\"productID\",\"appname\") VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s');"

#define SELECT_ALL_GUID "SELECT guid FROM %s ORDER BY guid ASC;"

enum e_value_type{
    E_VALUE_TYPE_INT,           /* value的类型：int */
    E_VALUE_TYPE_STRING         /* value的类型：string */
};

struct dev_value_type {
    char key_name[20];          /* key的名字 */
    enum e_value_type type;     /* value的类型：int、string */
};


int get_db_history_log_day(void);

int get_next_serial_num(char *t_name, int *p_num);

int db_begin(void);
int db_commit(void);
int db_vacuum(void);
int db_insert_into_table(char* str_sql);
int db_delete_table(char *str_sql);
int db_select_with_cb(char *str_sql, sqlite3_callback cb_fun, void *para);

int db_update_into_table(char* str_sql);

int get_log_config(int *en, int *mbyte, int *rsyslog_enable);
int read_json_config(void);
void database_init(void);
void database_destory(void);

int db_set_temp_store_directory(void);

int get_unused_guid_V2(char *table_name, char *guid);
int get_max_guid(char *table_name, void *para);
int save_property_to_dev_table_V2(char *t_name, struct dev_property_s *p);

int get_all_dev_guid(char *table_name, void *para);

#endif  /* __DATABASE_H__ */
